#!/usr/bin/python3
# -*- coding:utf-8 -*-
"""
@author: henry_oulen@163.com
@file: mysql_base.py
@time: 2023/10/26 15:07
@desc: 
"""
import copy
import json
import time

from sqlalchemy import create_engine, text, desc, inspect
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, declared_attr
from sqlalchemy.orm import sessionmaker
from tornado.options import options

from backend.conf import config
from backend.tvadmin.utils.log_utls import app_log

# from backend.tvadmin.utils.model_utils import ModelBase

host = config.mysql["host"]
port = config.mysql["port"]
user = config.mysql["user"]
password = config.mysql["password"]
database = config.mysql["database"]

# 数据连接url
db_url = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(user, password, host, port, database)


# Base = declarative_base()


class CustomBase:
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()


class SqlAlBase(object):
    engine = create_engine(db_url, echo=config.sqlalchemy_echo_log)
    session = scoped_session(sessionmaker(bind=engine))
    base = declarative_base(cls=CustomBase)

    # 类方法，不需要创建SqlAlBase对象即可调用，get_session方法用来获取session对象
    @classmethod
    def get_session(cls):
        return cls.session

    # 类方法，用来获取base
    # @classmethod
    # def get_base(cls):
    #     return cls.base


class MysqlBase():
    session = SqlAlBase.get_session()
    conn = None

    # 添加方法
    @classmethod
    def add(cls, model, request):
        try:
            cls.session.add(model)
            if hasattr(request, "user"):
                model.creator_id = request.user.id,
                model.creator_name = request.user.account,
            cls.session.commit()
            columns = inspect(model).mapper.column_attrs
            values = {c.key: getattr(model, c.key) for c in columns if c.key != "password"}
            return values

        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)

            cls.session.rollback()

        finally:
            cls.session.close()

    @classmethod
    def save(cls, model):
        try:
            cls.session.add(model)
            cls.session.commit()
            columns = inspect(model).mapper.column_attrs
            values = {c.key: getattr(model, c.key) for c in columns if c.key != "password"}
            return values

        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)

            cls.session.rollback()

        finally:
            cls.session.close()

    @classmethod
    def softdelete(cls, filter):
        try:
            cls.session.query(cls).filter(text(filter)).update({"is_delete": 0}, synchronize_session=False)
            cls.session.commit()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        finally:
            cls.session.close()

    @classmethod
    def query_first_dict(cls, **filter):
        try:
            if not filter:
                item = cls.session.query(cls).filter_by(is_delete=0).first()
                return item
            else:
                obj = cls.session.query(cls).filter_by(**filter).filter_by(is_delete=0).first()
                # item_dict = {k: v for k, v in item.items() if k != '_sa_instance_state'}
                # return item_dict
                columns = inspect(obj).mapper.column_attrs
                values = {c.key: getattr(obj, c.key) for c in columns}
                return values

        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def filter_list(cls, filter_args=None, **kwargs):
        try:
            filter_argss = copy.deepcopy(filter_args)
            if "page" in filter_argss:
                del filter_argss["page"]
            if "page_size" in filter_argss:
                del filter_argss["page_size"]
            item_list = cls.session.query(cls).filter_by(**filter_argss).filter_by(is_delete=0).order_by(
                desc(cls.create_datetime)).all()
            result_list = [{k: v for k, v in row.__dict__.items() if k != '_sa_instance_state' and k != "password"} for
                           row in item_list]
            return result_list
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def filter(cls, **kwargs):
        try:
            return cls.session.query(cls).filter_by(**kwargs).filter_by(is_delete=0).all()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def filter_old(cls, filter):
        try:
            return cls.session.query(cls).filter(**filter).filter_by(is_delete=0).all()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def filter_in(cls, key, filter):
        try:
            key = getattr(cls, key)
            if key:
                return cls.session.query(cls).filter(key.in_(filter)).filter_by(is_delete=0).all()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()

    @classmethod
    def first(cls, **kwargs):
        try:
            return cls.session.query(cls).filter_by(**kwargs).filter_by(is_delete=0).first()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        finally:
            pass
            # cls.session.close()

    @classmethod
    def all(cls):
        try:
            password = getattr(cls, "password")
            return cls.session.query(cls).filter_by(is_delete=0).with_entities(password).all()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)

            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def all_list(cls):
        try:
            # password = getattr(cls, "password")
            item_list = cls.session.query(cls).filter_by(is_delete=0).order_by(desc(cls.create_datetime)).all()
            item_list = [{k: v for k, v in row.__dict__.items() if k != '_sa_instance_state' and k != "password"} for
                         row in item_list]
            # item_list = [{k: v for k, v in row.__dict__.items() if k != '_sa_instance_state'} for
            #              row in item_list]

            return item_list
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def filter_update(cls, filter_args=None, update_args=None, **kwargs):
        try:
            if update_args:
                update_args["modifier_id"] = kwargs["request"].user.id
                update_args["modifier_name"] = kwargs["request"].user.account
                update_args["update_datetime"] = int(time.time())
                cls.session.query(cls).filter_by(**filter_args).update(update_args, synchronize_session="auto")
                cls.session.commit()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        # finally:
        #     cls.session.close()

    @classmethod
    def to_dict(cls):
        """将对象转换为dict.
        @return: dict
        """
        d = {}
        for column in cls.__name__.columns:
            d[column.name] = getattr(cls, column.name)
        return d

    @classmethod
    def to_json(cls):
        """将对象转换为JSON.
        @return: JSON data
        """
        return json.dumps(cls.to_dict())

    @classmethod
    def delete_soft(cls, **filter):
        try:
            cls.session.query(cls).filter_by(**filter).update({"is_delete": 1, "delete_datetime": int(time.time())})
            cls.session.commit()
        except SQLAlchemyError as e:
            app_log.error(f"error:{e}")
            print(e)
            cls.session.rollback()
        finally:
            cls.session.close()

    @classmethod
    def __repr__(cls) -> str:  # 通用写法
        fields = ['{0}={1}'.format(k, getattr(cls, k)) for k in cls._sa_class_manager.keys()]  # 方式一
        # fields = ['{0}={1}'.format(k, v) for k, v in self.__dict__.items() if not k.startswith("_")]  # 方式二
        # return "{}({})".format(cls.__class__.__tablename__, ", ".join(fields))
        return "{}({})".format(cls.__tablename__, ", ".join(fields))

    @declared_attr
    def __tablename_(cls):
        return cls.__name__.lower()

    @classmethod
    def get_column_names(cls):
        return [column.name for column in cls.__table__.columns]


# 数据库连接初始化
class MysqlSqlalchemy(object):
    def __init__(self):
        # 创建mysql连接引擎
        engine = create_engine(db_url, echo=options.MySQL_OperatorLog)
        self.session = scoped_session(sessionmaker(bind=engine))
